clear all
set more off, perm
local mydir "`1'"
global Tables	`mydir'/XS/Tables
global Work	`mydir'/XS/DataWork
global Raw	`mydir'/XS/DataRaw
global WRDS	`mydir'/WRDS
/* ********************************************************************* */
* figure out whether security is common stock
import sas $WRDS/CRSP/stocknames.sas7bdat, clear case(lower)
keep permno permco comnam namedt nameenddt siccd shrcd exchcd 
sort permco permno nameenddt
keep if (shrcd==10 | shrcd==11)
keep if exchcd>0 & exchcd<4

* find end-of-the-month day for the last record of each stock
by permco permno:  gen obs=_n
by permco permno:  gen tot=_N
replace nameenddt=mdy(12,31,year(nameenddt)) 			if month(nameenddt)==12 & obs==tot
replace nameenddt=mdy(month(nameenddt)+1,1,year(nameenddt))-1 	if month(nameenddt)<=11 & obs==tot

drop obs tot

tempfile header
save "`header'", replace
/* ********************************************************************* */
import sas $WRDS/CRSP/msf.sas7bdat, clear case(lower)
keep permno permco date prc shrout ret retx

joinby permco permno using "`header'"
keep if date>=namedt & date<=nameenddt

* some duplicates because --for example-- different siccd/comnam
duplicates drop permco permno shrcd prc date ret retx, force

* sanity check: no duplicates
duplicates tag permco permno date, gen(dup)
tabulate dup

gen mcap=abs(prc)*shrout 
drop namedt nameenddt dup shrout prc

gen datem=mofd(date)
format datem %tm

tempfile msf
save "`msf'", replace
* *****************************
sort permco permno datem 
collapse (lastnm) lastmcap=mcap, by(permco permno)

tempfile lastmcap
save "`lastmcap'", replace
* *****************************
* Work with firms that stopped trading, i.e. exclude firms that are still trading
* #1) get data on delisting codes and returns
import sas $WRDS/CRSP/msedelist.sas7bdat, clear case(lower)
keep permco permno dlstdt dlstcd dlret dlretx dlpdt
gen datem=mofd(dlstdt)
format datem %tm

merge 1:1 permco permno datem using "`msf'"
keep if _merge==3
drop _merge

* #2) exclude firms that are still trading
drop if dlstcd==100 | date==mdy(12,31,2022)

* #3) do Shumway adjustment for financially-distressed firms with missing delisting returns
replace dlret =-0.3 if missing(dlret)  & dlstcd>=400 & dlstcd<600
replace dlretx=-0.3 if missing(dlretx) & dlstcd>=400 & dlstcd<600

preserve 
* #4) select observations for which last distribution coincides with last date on msf.dta
* Do Cohen, Polk, Vuolteenaho (JF 2003) adjustment: When computing firms’ stock returns, we include delisting data when available on the CRSP tapes. 

keep if mofd(dlpdt)==mofd(dlstdt) | dlpdt==.

* update mcap using last nonmissing value
merge 1:1 permco permno using "`lastmcap'"
drop _merge

replace   ret=(1+dlret)*(1+ret)-1	if dlret+ret<.
replace   ret=dlret			if ret==.

replace   retx=(1+dlret)*(1+retx)-1	if dlret+retx<.
replace   retx=dlret			if retx==.
replace   mcap=lastmcap 		if mcap==.

drop dlstdt dlstcd dlret dlretx lastmcap

gen source="no-gap"

tempfile nogap
save "`nogap'", replace
*******************
restore
* #5) select observations for which last distribution happens after ast date on msf.dta; spread delisting return
* Do Cohen, Polk, Vuolteenaho (JF 2003) adjustment.  
* When computing firms’ stock returns, we include delisting data when available on the CRSP tapes. In some cases, CRSP records delisting prices several months after
* the security ceases trading and, thus, after a period of missing returns. In these cases, we calculate the total return from the last available price to the 
* delisting price and pro-rate this return over the intervening months. Delisting return is included when computing the market value at which the ¢rm exits.

keep if mofd(dlstdt)<mofd(dlpdt) & dlpdt+dlret<.

* update mcap using last nonmissing value
merge 1:1 permco permno using "`lastmcap'"
drop _merge

* fill in dates to pro-rate returns
gen n=mofd(dlpdt)-mofd(dlstdt)+1
expand n, gen(new)

sort permno datem new
replace datem=datem[_n-1]+1 if permno==permno[_n-1]
replace date =mdy(12,31,year(dofm(datem))) 			if month(dofm(datem))==12 & new==1
replace date =mdy(month(dofm(datem))+1,1,year(dofm(datem)))-1 	if month(dofm(datem))<=11 & new==1

* sanity check:  make sure end date does not exceed date for last observation in CRSP
drop if dofm(datem)>=mdy(1,1,2023)

drop n
by permno: gen n=_N

sort permno datem
replace   ret=(1+ret)*(1+dlret/n)-1	if new==0 & (dlret+ret)<.
replace   ret=dlret/n			if new==1 | (new==0 & ret==.)

replace   retx=(1+retx)*(1+dlret/n)-1	if new==0 & (dlret+retx)<.
replace   retx=dlret/n			if new==1 | (new==0 & retx==.)

replace   mcap=lastmcap*(1+retx)	if new==0 & mcap==.
replace   mcap=mcap[_n-1]*(1+retx)	if new==1 
gen source="gap"

drop dlstdt dlstcd dlret dlretx lastmcap n new
append using "`nogap'"
sort permco permno datem

* Put it all back together.  
merge 1:1 permco permno datem using "`msf'"
sort permco permno datem
drop _merge

replace source="msf" if length(source)==0

* keep only common stock
* 1st digit of "shrcd"
*1	Ordinary Common Shares
*2	Certificates
*3	ADRs (American Depository Receipts)
*4	SBIs (Shares of Beneficial Interest)
*7	Units (Depository Units, Units of Beneficial Interest, Units of Limited Partnership Interest, Depository Receipts, etc.)

* 2nd digit of "shrcd"
*0	Securities which have not been further defined.
*1	Securities which need not be further defined.
*2	Companies incorporated outside the US
*3	Americus Trust Components (Primes and Scores).
*4	Closed-end funds.
*5	Closed-end fund companies incorporated outside the US
*8	REIT's (Real Estate Investment Trusts)

keep if (shrcd==10 | shrcd==11)
drop shrcd

save $Work/msf_adjusted, replace
* ********************************************************
